# coding: utf-8

# 更新微信取关用户是否充过值等信息

import os
import sys
import set_env_path     # 请勿删除

from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def update_cancel_attention_users():
    """
    add by zhouhl 2017-09-14
    """

    select_cancel_attention_users = text("""
        select id,uid from cancel_attention_users
    """)

    update_sql = text("""
        update cancel_attention_users set
            has_recharge=:has_recharge,
            has_partic=:has_partic,
            has_pack=:has_pack
        where uid=:uid
    """)

    # 查询充值记录
    select_user_bill = text("""
        select count(1) as count from bill
        where
            uid=:uid and type=1 and sub_type=2 and status=200 and deleted_at=0
    """)

    # 查询抢单记录
    select_user_partic = text("""
        select count(1) as count from partic where supplier_id=:uid
    """)

    # 查询用户套餐是否可用
    select_user_pack = text("""
        select id,call_point from pack where uid=:uid
    """)

    records = conn.execute(select_cancel_attention_users).fetchall()

    for i in records:

        recharge_count = conn.execute(select_user_bill, uid=i.uid).fetchone()
        has_recharge = 1 if recharge_count.count>0 else 0

        partic_count = conn.execute(select_user_partic, uid=i.uid).fetchone()
        has_partic = 1 if partic_count.count>0 else 0

        pack_count = conn.execute(select_user_pack, uid=i.uid).fetchone()
        has_pack = 1 if pack_count and pack_count.call_point>0 else 0

        conn.execute(
            update_sql,
            has_recharge=has_recharge,
            has_partic=has_partic,
            has_pack=has_pack,
            uid=i.uid,
        )


if __name__ == '__main__':
    update_cancel_attention_users()
